﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Management.Instrumentation;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Picea.UI.Properties;

namespace Picea.Core
{
    public static class SqlHelper
    {
        public static readonly string PiceaConnection =
            ConfigurationManager.ConnectionStrings["piceaBD"].ConnectionString;

        /// <summary>
        /// Envia um Comando Sql que retorna um DataReader da Base.
        /// Autor: Celso Vinicius do Nascimento
        /// </summary>
        /// <param name="stConnectionString">a String de Connexão</param>
        /// <param name="sqlCommand">O commando a ser executado</param>
        /// <returns>SqlReader</returns>
        public static SqlDataReader ExecuteReader(string stConnectionString, string sqlCommand)
        {
            SqlConnection conn = new SqlConnection(stConnectionString);

            SqlCommand command = new SqlCommand(sqlCommand, conn);

            SqlDataReader reader;
            try
            {
                conn.Open();
                reader = command.ExecuteReader();
                conn.Close();
                return reader;

            }
            catch (Exception ex)
            {

                throw ex;
            }
            
        }

        /// <summary>
        /// Envia um commando sql e retona uma int com o número de linhas afetadas
        /// Autor: Celso Vinicius do Nascimento
        /// </summary>
        /// <param name="stConnectionString">A String de Conexão</param>
        /// <param name="sqlCommand">o commando non query a ser executado</param>
        /// <returns>int - número de linhas afetadas</returns>
        public static int ExecuteNonQuery(string stConnectionString, string sqlCommand)
        {
            SqlConnection conn = new SqlConnection(stConnectionString);

            SqlCommand command = new SqlCommand(sqlCommand, conn);
            int nRetorno;
            try
            {
                conn.Open();
                nRetorno = command.ExecuteNonQuery();
                conn.Close();
                return nRetorno;

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        /// <summary>
        /// Envia um comando sql e retorna retorna o escalar deste comando
        /// </summary>
        /// <param name="stConnectionString">string de conexão</param>
        /// <param name="sqlCommand">Comando sql a ser executado</param>
        /// <returns>objeto escalar</returns>
        public static object ExecuteScalar(string stConnectionString, string sqlCommand)
        {
            SqlConnection conn = new SqlConnection(stConnectionString);

            SqlCommand command = new SqlCommand(sqlCommand, conn);

            object retornoScalar;

            try
            {
                conn.Open();
                retornoScalar = command.ExecuteScalar();
                conn.Close();
                return retornoScalar;

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
    }
}
